<?php


namespace app\admin\controller;


use app\admin\model\CemeteryModel;
use cmf\controller\AdminBaseController;
use think\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\Exception;
use think\exception\DbException;
use tree\Tree;

class AnalysisController extends AdminBaseController
{
    protected $selected_id_v = 0;

    /**
     * 其他收支统计
     * @return mixed
     */
    public function inComeIndex()
    {
        header('Access-Control-Allow-Origin: *');
//        try {
        $data1 = $this->request->param();
        if ($this->request->isPost()) {
            $param = $this->request->param("type");
            $itype = $this->request->param("itype");
            $startTime = $this->request->param("start_time");
            $endTime = $this->request->param("end_time");
            $jingBanRen = $this->request->param("jingBanRen");
            $xiaDanRen = $this->request->param("xiaDanRen");

//            $param = $data1['type'];
//            $itype = $data1['itype'];
//            $startTime = $data1['start_time'];
//            $endTime = $data1['end_time'];
//            $jingBanRen = $data1['jingBanRen'];
//            $xiaDanRen = $data1['xiaDanRen'];
//        echo 1 ;
//        echo $param;
//            if (empty($startTime) || empty($endTime)) {
//                $start = strtotime(date("Y-m-d", time())) - 3600 * 24;
//                $end = strtotime(date("Y-m-d", time())) - 1;
////            echo $start ."-".$end;
//            } else {
//                $start = strtotime($startTime);
//                $end = strtotime($endTime);
////            echo $start ."-".$end;
//            }
            if (empty($startTime) || empty($endTime)) {
                $start = 0;
                $end = 9999999999;
//                    $start = strtotime(date("Y-m-d", time())) - 3600 * 24;
//                    $end = strtotime(date("Y-m-d", time())) - 1;
//            $start = strtotime(date("Y-m-d", time())) - 3600 * 24;
//            $end = strtotime(date("Y-m-d", time())) - 1;
//            echo $start ."-".$end;
            } else {
                $start = strtotime($startTime);
                $end = strtotime($endTime) + 3600 * 24 - 1;
            }


            $where = array();
//        $where["p.itype"] = array('in', [3, 6, 7, 8, 9]);
            if (!empty($itype)) {
                $where['pay.itype'] = [$itype];
            }
            if (!empty($jingBanRen)) {
                $where['o.handle'] = [$jingBanRen];
            }
            if (!empty($xiaDanRen)) {
                $where['o.handle'] = [$xiaDanRen];
            }
            $month = date('m');
            $year = date('Y');
            $last_year = date('Y') - 1;
            $next_year = date('Y') + 1;
            $last_month = date('m') - 1;
            $next_month = date('m') + 1;
            if ($month == 1) {
                $last_month = 12;
                $year = $year - 1;
            }
            if ($param == 1) {
                //获取昨天的开始时间戳
                $start = strtotime(date("Y-m-d", time())) - 3600 * 24;
                //获取昨天结束时间戳
                $end = strtotime(date("Y-m-d", time())) - 1;
            } elseif ($param == 2) {
                //获取今天开始的时间戳
                $start = strtotime(date("Y-m-d", time())) - 0;
                //获取今天结束的时间戳
                $end = strtotime(date("Y-m-d", time())) + 3600 * 24 - 1;
            } elseif ($param == 3) {
                //获取上月开始的时间戳
                $start = mktime(0, 0, 0, $last_month, 1, $year);
                $end = mktime(0, 0, 0, $month, 1, $year) - 1;
            } elseif ($param == 4) {
                //获取本月开始的时间戳
                $start = mktime(0, 0, 0, $month, 1, $year);
                $end = mktime(0, 0, 0, $next_month, 1, $year) - 1;
            } elseif ($param == 5) {
                //获取上一年的时间戳
                $start = mktime(0, 0, 0, 1, 1, $last_year);
                $end = mktime(0, 0, 0, 1, 1, $year) - 1;
            } elseif ($param == 6) {
                //获取本年的时间戳
                $start = mktime(0, 0, 0, 1, 1, $year);
                $end = mktime(0, 0, 0, 1, 1, $next_year) - 1;
            }
            $dealSum = 0.0;
            $flowingSum = 0.0;
//                $dataCount = Db::name("order_pay_log")->alias("pay")
//                    ->field("o.order_id")
//                    ->leftJoin("order o", "o.order_id = pay.order_id ")
//                    ->leftJoin("customer_info info", "pay.customer_id = info.id")
//                    ->leftJoin("tombarea_information_product pro", "o.tomb_product_id = pro.id")
//                    ->leftJoin("user u", "pay.handle = u.id")
//                    ->leftJoin("department d", "u.dept_id = d.id")
//                    ->whereBetween("pay.add_time", $start . "," . $end)
//                    ->where("pay.itype", "<>", 0)
//                    ->where($where)
//                    ->select();
            if ($itype == 3) {
                // 落葬列表
                $sql = Db::name("service_buried")->alias("sb")
                    ->field("p.tomb_region,pay.itype,o.tomb_type,o.subscription_area,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,col.num tomb_num,p.selling_price,pay.deal_price,o.closing_price,info.buyer_name,
                   sb.service_status sbStatus,u.user_nickname,uss.user_nickname shoukuanren ,de.department_name")
                    ->join("tombarea_row_col col", "col.colID = sb.tomb_id ", "left")
                    ->join("order o", "o.order_id = sb.order_id", "left")
                    ->join("tombarea_information_product p", "p.id = o.tomb_product_id", "left")
                    ->join("order_pay_log pay", "pay.id = sb.pay_id", "left")
                    ->join("customer_info info", "o.customer_id = info.id", "left")
//                    ->join("archives_deceased ad", "ad.tomb_id = col.colID", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user uss", "pay.pay_real_name = uss.id")
                    ->leftJoin("user us", "sb.receive_user = us.id")
                    ->leftJoin("dj_department de", "de.id = us.dept_id")
                    ->where("pay.itype", 3)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)
                    ->whereBetween("o.closing_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql->paginate(10, false, ['query' => request()->param()]);
//                echo Db::name("service_buried")->getLastSql();die;
                $dataCount = $sql->select();
            } elseif ($itype == 8) {
                // 迁墓列表
                $sql = Db::name("service_move")->alias("sb")
                    ->field("p.tomb_region,pay.itype,o.tomb_type,o.subscription_area,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,col.num tomb_num,p.selling_price,pay.deal_price,o.closing_price,info.buyer_name,
                    sb.service_status sbStatus,u.user_nickname,uss.user_nickname shoukuanren ,de.department_name")
                    ->join("tombarea_row_col col", "col.colID = sb.tomb_id ", "left")
                    ->join("order o", "o.order_id = sb.order_id", "left")
                    ->join("tombarea_information_product p", "p.id = o.tomb_product_id", "left")
                    ->join("order_pay_log pay", "pay.id = sb.pay_id", "left")
                    ->join("customer_info info", "o.customer_id = info.id", "left")
//                    ->join("archives_deceased ad", "ad.tomb_id = col.colID", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user uss", "pay.pay_real_name = uss.id")
                    ->leftJoin("user us", "sb.receive_user = us.id")
                    ->leftJoin("dj_department de", "de.id = us.dept_id")
                    ->where("pay.itype", 8)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)
                    ->whereBetween("o.closing_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql
                    ->paginate(10, false, ['query' => request()->param()]);
//                echo Db::name("service_buried")->getLastSql();die;
                $dataCount = $sql->select();
            } elseif ($itype == 2 || $itype == 12) {
                // 常规维保列表
                $sql = Db::name("service_weibao")->alias("sb")
                    ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,p.tomb_num,p.selling_price,pay.deal_price,pay.free_price,info.buyer_name,
                    sb.service_status sbStatus,u.user_nickname,uss.user_nickname shoukuanren ,de.department_name")
//                    ->join("tombarea_row_col col", "col.colID = sb.tomb_id ", "left")
//                        ->join("order o", "o.order_id = sb.order_id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = sb.tomb_id", "left")
                    ->join("order_pay_log pay", "pay.id = sb.pay_id", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user uss", "pay.pay_real_name = uss.id")
                    ->leftJoin("user us", "sb.receive_user = us.id")
                    ->leftJoin("dj_department de", "de.id = us.dept_id")
                    ->where("pay.itype", $itype)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)
                    ->whereBetween("pay.add_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql
                    ->paginate(10, false, ['query' => request()->param()]);
                $dataCount = $sql->select();
            } elseif ($itype == 7) {
                $sql = Db::name("order_pay_log")->alias("pay")
                    ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,p.tomb_num,pay.total_price,info.buyer_name,
                    u.user_nickname,uss.user_nickname shoukuanren ")
//                    ->join("tombarea_row_col col", "col.colID = sb.tomb_id ", "left")
//                        ->join("order o", "o.order_id = sb.order_id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user uss", "pay.pay_real_name = uss.id")
                    ->where("pay.itype", $itype)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)
                    ->whereBetween("pay.add_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql
                    ->paginate(10, false, ['query' => request()->param()]);
                $dataCount = $sql->select();
            } elseif ($itype == 9) {
                // 商品列表
                $sql = Db::name("order_pay_log")->alias("pay")
                    ->field("FROM_UNIXTIME(pay.add_time) add_time,pay.itype,
                    pay.deal_price,pay.total_price,
                   u.user_nickname,us.user_nickname shoukuanren ")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user us", "pay.pay_real_name = us.id")
                    ->where("pay.itype", 9)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)
                    ->whereBetween("pay.add_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql
                    ->paginate(10, false, ['query' => request()->param()]);
                $dataCount = $sql->select();
            } elseif ($itype == 4) {
                // 维护费
                $sql = Db::name("protect_payment")->alias("sd")
                    ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,p.tomb_num,p.selling_price,pay.free_price,pay.deal_price,info.buyer_name,
          sd.pay_status status,FROM_UNIXTIME(sd.end_time) end_time,sd.end_time endTime ,u.user_nickname,us.user_nickname shoukuanren")
                    ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user us", "pay.pay_real_name = us.id")
                    ->where("pay.itype", 4)
                    ->where("pay.is_delete", 0)
                    ->whereBetween("pay.add_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql
                    ->paginate(10, false, ['query' => request()->param()]);
                $dataCount = $sql->select();
            } elseif ($itype == 11) {
                //换碑
                $sql = Db::name("order_pay_log")->alias("pay")
                    ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,p.tomb_num,pay.tom_price,pay.free_price,pay.deal_price,info.buyer_name,
                    u.user_nickname,uss.user_nickname shoukuanren ")
//                    ->join("tombarea_row_col col", "col.colID = sb.tomb_id ", "left")
//                        ->join("order o", "o.order_id = sb.order_id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user uss", "pay.pay_real_name = uss.id")
                    ->where("pay.itype", $itype)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)
                    ->whereBetween("pay.add_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql
                    ->paginate(10, false, ['query' => request()->param()]);
                $dataCount = $sql->select();
            } else {
                $sql = Db::name("order_pay_log")->alias("pay")
                    ->field("FROM_UNIXTIME(pay.add_time) add_time,o.tomb_type,o.selling_price,o.tomb_typei,pay.itype,o.subscription_area,o.closing_price,pay.handle,info.buyer_name,pro.tomb_region,pro.tomb_address,
            pay.tomb_num,u.user_nickname,d.department_name,pay.total_price,pay.deal_price,pay.pay_real_name,us.user_nickname shoukuanren")
                    ->leftJoin("order o", "o.order_id = pay.order_id ")
                    ->leftJoin("customer_info info", "info.id = pay.customer_id ")
                    ->leftJoin("tombarea_information_product pro", "pro.tomb_id = pay.tomb_id")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->leftJoin("user us", "pay.pay_real_name = us.id")
                    ->leftJoin("department d", "u.dept_id = d.id")
                    ->where("pay.itype", "<>", 0)
                    ->where("pay.itype", "<>", 1)
                    ->where("pay.itype", "<>", 5)
                    ->whereBetween("pay.add_time", $start . "," . $end)
                    ->where($where)
                    ->order("pay.add_time", "desc");
                $data = $sql
                    ->paginate(10, false, ['query' => request()->param()]);
                $dataCount = $sql->select();
            }

//                $page = $data->render();
//                echo Db::name("order_pay_log")->getLastSql();
//        dump($data);
//                echo count($data) .'--'.count($dataCount);die;
            for ($i = 0; $i < count($dataCount); $i++) {
                if ($dataCount[$i]['itype'] != 2 && $dataCount[$i]['itype'] != 12 && $dataCount[$i]['itype'] != 9 && $dataCount[$i]['itype'] != 11 && $dataCount[$i]['itype'] != 6 && $dataCount[$i]['itype'] != 4 && $dataCount[$i]['itype'] != 7) {
                    if ($dataCount[$i]['tomb_type'] == 2) {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['selling_price']) * floatval($dataCount[$i]['subscription_area']);
                        $dealSum = floatval($dealSum) + floatval($dataCount[$i]['closing_price']);
                    } else if ($dataCount[$i]['tomb_type'] == 4) {
                        $flowingSum = floatval($flowingSum) + floatval($dataCount[$i]['selling_price']);
                        $dealSum = floatval($dealSum) + floatval($dataCount[$i]['closing_price']);
                    } else if ($dataCount[$i]['tomb_type'] == 1 || $dataCount[$i]['tomb_type'] == 3) {
                        $flowingSum = floatval($flowingSum) + floatval($dataCount[$i]['selling_price']);
                        $dealSum = floatval($dealSum) + floatval($dataCount[$i]['closing_price']);
                    }
                } else {
                    if ($dataCount[$i]['itype'] == 9) {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['total_price']);
                        $dealSum = $dealSum + floatval($dataCount[$i]['total_price']);
                    } elseif ($dataCount[$i]['itype'] == 4) {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['free_price'] + $dataCount[$i]['deal_price']);
                        $dealSum = $dealSum + floatval($dataCount[$i]['deal_price']);
                    } elseif ($dataCount[$i]['itype'] == 6) {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['total_price']);
                        $dealSum = $dealSum + floatval($dataCount[$i]['total_price']);
                    } elseif ($dataCount[$i]['itype'] == 2 || $dataCount[$i]['itype'] == 12) {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['free_price'] + $dataCount[$i]['deal_price']);
                        $dealSum = $dealSum + floatval($dataCount[$i]['deal_price']);
                    } elseif ($dataCount[$i]['itype'] == 7) {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['total_price']);
                        $dealSum = $dealSum + floatval($dataCount[$i]['total_price']);

                    } elseif ($dataCount[$i]['itype'] == 11) {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['free_price'] + $dataCount[$i]['deal_price']);
                        $dealSum = $dealSum + floatval($dataCount[$i]['deal_price']);
                    } else {
                        $flowingSum = $flowingSum + floatval($dataCount[$i]['total_price']);
                        $dealSum = $dealSum + floatval($dataCount[$i]['deal_price']);
                    }

                }

            }
            //获取经办人
            $jingBanRenData = Db::name("user")->alias("u")
                ->field("u.id,concat(d.department_name,'-',u.user_nickname) user_nickname ,d.dept_type")
                ->leftJoin("department d", "u.dept_id = d.id")
                ->where("d.dept_type", "in", [1, 3])
                ->select()->toArray();
            // 获取下单人
            $xiaDanRenData = Db::name("user")->field("id,user_nickname")->where("if_diding", 1)->select()->toArray();

//        } catch (Exception $e) {
//            $this->error($e->getMessage());
//        }
            $sum['flowingSum'] = $flowingSum;
            $sum['dealSum'] = $dealSum;
            $sum['discount'] = $flowingSum - $dealSum;
            $datas = [];
            $datas['data'] = $data;
            $datas['sum'] = $sum;
            $datas['jingBanRenData'] = $jingBanRenData;
            $datas['xiaDanRenData'] = $xiaDanRenData;
            $datas['dataCount'] = $dataCount->count();
//            print_r($datas);die;
            $this->success("成功", "", $datas);
//            $this->assign("data", $data);
//            $this->assign("page", $page);
//            $this->assign("sum", $sum);
//            $this->assign("dataCount", $dataCount->count());
        }
        return $this->fetch("incomeindex");
//        } catch (Exception $e) {
//            $this->error($e->getMessage());
//        }
    }

    /**
     * 销售统计
     * @return mixed
     */
    public function saleStatistics()
    {
        try {
            $data = $this->request->param();
//            if ($data) {
            if ($this->request->isPost()) {
                $param = $data['type'];
                $shoppingguide = $data['shoppingguide'];
                $daili = $data['daili'];
                $didingren = $data['didingren'];
                $startTime = $data['start_time'];
                $endTime = $data['end_time'];
                $cate_id = $data['cate_id'];
                if (empty($startTime) || empty($endTime)) {
                    $start = 0;
                    $end = 9999999999;

                } else {
                    $start = strtotime($startTime);
                    $end = strtotime($endTime) + 3600 * 24 - 1;
                }
                $where = array("p.is_delete" => 0, "p.type" => 2, "p.refund_state" => 0);
                if (!empty($daili)) {
                    $where['o.agent_user'] = [$daili];
                }
                if (!empty($shoppingguide)) {
                    $where['o.shoppingguide'] = [$shoppingguide];
                }
                if (!empty($cate_id)) {
                    $where['co.cate_id'] = [$cate_id];
                }
//                if (!empty($daili)) {
//                    $where['agent_user'] = [$daili];
//                }
                if (!empty($didingren)) {
                    $where['co.reacher'] = [$didingren];
                }
                $month = date('m');
                $year = date('Y');
//        $last_year = date('Y') - 1;
//        $next_year = date('Y') + 1;
                $last_month = date('m') - 1;
                $next_month = date('m') + 1;
                $orderData = Db::name("order")->alias("o")
                    ->field("o.tomb_type,o.tomb_typei,FROM_UNIXTIME(o.closing_time) closing_time,info.buyer_name,o.subscription_area,pro.tomb_region,pro.tomb_address,
            pro.tomb_num,o.selling_price,o.discount_ratio,o.closing_price,u.user_nickname daili,us.user_nickname daogou,co.reacher")
                    ->leftJoin("customer_info info", "o.customer_id = info.id")
                    ->leftJoin("tombarea_information_product pro", "o.tomb_product_id = pro.id")
                    ->leftJoin("user u", "o.agent_user = u.id")
                    ->leftJoin("user us", "o.shoppingguide = us.id")
                    ->leftJoin("tombarea_row_col co", "o.tomb_id = co.colID")
                    ->leftJoin("order_pay_log p", "p.order_id  = o.order_id")
                    ->whereBetween("o.closing_time", $start . "," . $end)
                    ->where($where);
                if ($month == 1) {
                    $last_month = 12;
                    $year = $year - 1;
                }
                if ($param == 1) {
                    //获取昨天的开始时间戳
                    $start = strtotime(date("Y-m-d", time())) - 3600 * 24;
                    //获取昨天结束时间戳
                    $end = strtotime(date("Y-m-d", time())) - 1;
                    $orderData = $orderData->whereBetween("o.closing_time", $start . "," . $end);
                } elseif ($param == 2) {
                    //获取今天开始的时间戳
                    $start = strtotime(date("Y-m-d", time())) - 0;
                    //获取今天结束的时间戳
                    $end = strtotime(date("Y-m-d", time())) + 3600 * 24 - 1;
                    $orderData = $orderData->whereBetween("o.closing_time", $start . "," . $end);
                } elseif ($param == 3) {
                    //获取上月开始的时间戳
                    $start = mktime(0, 0, 0, $last_month, 1, $year);
                    $end = mktime(0, 0, 0, $month, 1, $year) - 1;
                    $orderData = $orderData->whereBetween("o.closing_time", $start . "," . $end);
                } elseif ($param == 4) {
                    //获取本月开始的时间戳
                    $start = mktime(0, 0, 0, $month, 1, $year);
                    $end = mktime(0, 0, 0, $next_month, 1, $year) - 1;
                    $orderData = $orderData->whereBetween("o.closing_time", $start . "," . $end);
                }
                try {
                    $orderData = $orderData->order("o.closing_time", "desc")->paginate(10, false, ['query' => request()->param()]);
                } catch (DbException $e) {
                    echo $e->getMessage();
                }
                $page = $orderData->render();
//                echo Db::name("order")->getLastSql();
                //销售流水
                $flowingSum = 0.0;
                //成交金额
                $dealSum = 0.0;
                //返利累计
                $rebateSum = 0.0;
                //获取订单数据条数
                $yesterdayOrderData = Db::name("order")->alias("o")
                    ->field("o.order_id")
                    ->leftJoin("customer_info info", "o.customer_id = info.id")
                    ->leftJoin("tombarea_information_product pro", "o.tomb_product_id = pro.id")
                    ->leftJoin("user u", "o.agent_user = u.id")
                    ->leftJoin("tombarea_row_col co", "o.tomb_id = co.colID")
                    ->leftJoin("order_pay_log p", "p.order_id  = o.order_id")
                    ->where($where)
                    ->whereBetween("o.closing_time", $start . "," . $end)
                    ->select();
                //
                $flowing = Db::name("order_pay_log")->alias("p")
                    ->field("p.tom_price,o.tomb_type,o.agent_user,o.tomb_typei,o.subscription_area,p.deal_price")
                    ->leftJoin("order o", " o.order_id = p.order_id")
                    ->leftJoin("tombarea_row_col co", "o.tomb_id = co.colID")
                    ->where($where)
                    ->whereBetween("o.closing_time", $start . "," . $end)
                    ->select();
                for ($i = 0; $i < count($flowing); $i++) {
//                echo $flowing[$i]['tom_price'].' ';
                    if ($flowing[$i]['tomb_type'] == 2) {
                        $flowingSum = $flowingSum + floatval($flowing[$i]['tom_price']) * floatval($flowing[$i]['subscription_area']);
                        $dealSum = floatval($dealSum) + floatval($flowing[$i]['deal_price']);
                    } else if ($flowing[$i]['tomb_type'] == 4 && $flowing[$i]['tomb_typei'] == 0) {
                        $flowingSum = floatval($flowingSum) + floatval($flowing[$i]['tom_price']);
                        $dealSum = floatval($dealSum) + floatval($flowing[$i]['deal_price']);
                    } else if ($flowing[$i]['tomb_type'] == 1 || $flowing[$i]['tomb_type'] == 3) {
                        $flowingSum = floatval($flowingSum) + floatval($flowing[$i]['tom_price']);
                        $dealSum = floatval($dealSum) + floatval($flowing[$i]['deal_price']);
                    }
                    if ($flowing[$i]['agent_user'] != 0) {
                        $rebateSum = $rebateSum + floatval($flowing[$i]['deal_price']) * 0.16;
                    }
                }
                //查询导购员
                $daogouyuan = Db::name("user")
                    ->alias("u")
                    ->field("u.id,u.user_nickname")
                    ->join("department d", "u.dept_id = d.id", "left")
                    ->where(array("d.dept_type" => "1"))
                    ->select()->toArray();
//                print_r($daogouyuan);die;
                //查代理商
                $dailishang = Db::name("user")
                    ->alias("u")
                    ->field("u.id,u.user_nickname")
                    ->join("department d", "u.dept_id = d.id", "left")
                    ->where(array("d.dept_type" => "8"))
                    ->select()->toArray();
                //查抵顶人
                $didingren = Db::name("user")
                    ->alias("u")
                    ->field("u.id,u.user_nickname")
                    ->join("department d", "u.dept_id = d.id", "left")
                    ->where(array("d.dept_type" => "7"))
                    ->select()->toArray();
//                $sql = Db::name("order_pay_log")->getLastSql();echo $sql;die;
                if (empty($yesterdayOrderData)) {
                    $yesterdayOrderData['sum'] = 0;
                }
                $Statistics['OrderData'] = $yesterdayOrderData->count();
                $Statistics['DealData'] = $dealSum;
                $Statistics['discount'] = $flowingSum - $dealSum;
                $Statistics['flowing'] = $flowingSum;
                $Statistics['rebateSum'] = $rebateSum;
                $Statistics['balance'] = $dealSum - $rebateSum;
                $datas = [];
                $datas['statistics'] = $Statistics;
                $datas['daogouyuan'] = $daogouyuan;
                $datas['dailishang'] = $dailishang;
                $datas['didingren'] = $didingren;
                $datas['orderData'] = $orderData;
                $datas['page'] = $page;
//                print_r($datas);die;
                $this->success("成功", "", $datas);
            }
            return $this->fetch("saleindex");
        } catch (Exception $e) {
            echo "cuole";
        }
    }

    /**
     * 业务统计
     * @return mixed
     */
    public function serviceStatistics()
    {
        try {
            $data = $this->request->param();
            if ($this->request->isPost()) {
                $tombId = json_decode($data['tombId']);
                $jingBanRen = $data['jingBanRen'];
                $cate_id = $data['cate_id'];
                $startTime = $data['start_time'];
                $endTime = $data['end_time'];
                $param = $data['type'];
                $status = intval($data['status']);
                $this->cemetery_model = Db::name("cemetery");
//                $name = $this->request->param("name");
                //import("Tree");
                $menu = new Tree();
                $menu->icon = array('&nbsp;', '&nbsp;', '&nbsp;');
                $menu->nbsp = '';
                if (empty($name)) {

                    $result = $this->cemetery_model->field("id,name,parent_id as pId,type")->where(array('is_delete' => 0))
                        ->order(array("list_order" => "ASC"))->select()->toArray();
                } else {
                    $result = $this->cemetery_model->alias("c")
                        ->field("c.id,c.name,c.parent_id as pId,c.type")
                        ->join("dj_tombarea t", "c.id = t.cemetery_id", "left")
                        ->join("dj_tombarea_row r", "t.id = r.tombarea_id", "left")
                        ->join("dj_tombarea_row_col rc", "r.rowID = rc.rowid", "left")
                        ->where('c.is_delete', 0)
                        ->where('rc.num', $name)
                        ->order(array("list_order" => "ASC"))
                        ->select()->toArray();
                }
                $tom = array();
                foreach ($result as $k => $v) {
                    $tombarea = Db::name("tombarea")->field("id,name,'' as type")
                        ->where(array("cemetery_id" => $v["id"], "status" => 1))->order('list_order asc ')
                        ->select()->toArray();
                    foreach ($tombarea as $i => $t) {
                        $tombarea[$i]['pId'] = $v["id"];
                    }
                    $tom = array_merge_recursive($tom, $tombarea);
                }
                $result = array_merge_recursive($tom, $result);

//                $this->assign("categorys", json_encode($result));
//                $this->assign("name", $name);
                $where = array();
                if (!empty($tombId)) {
                    $where['p.tombar_id'] = $tombId;
                }
                if (!empty($jingBanRen)) {
                    $where['pay.handle'] = [$jingBanRen];
                }
                if (!empty($cate_id)) {
                    $where['col.cate_id'] = [$cate_id];
                }
                if ($status > -1) {
                    if ($status == 4) {
                        $where['o.whq_end_date'] = ['<', time()];
                    } else if ($status == 0) {
                        $where['sd.type'] = [$status];
                    } else if ($status == 1) {
                        $where['sd.type'] = [$status];
                    } else if ($status == 3) {
                        $where['pay.itype'] = [$status];
                    } else {
//                        $where['pay.itype'] = [$status];
                    }
                }
                if (empty($startTime) || empty($endTime)) {
                    $start = 0;
                    $end = 9999999999;

                } else {
                    $start = strtotime($startTime);
                    $end = strtotime($endTime) + 3600 * 24 - 1;
                }
                $month = date('m');
                $year = date('Y');
                $last_year = date('Y') - 1;
                $next_year = date('Y') + 1;
                $last_month = date('m') - 1;
                $next_month = date('m') + 1;
                if ($month == 1) {
                    $last_month = 12;
                    $year = $year - 1;
                }
                if ($param == 1) {
                    //获取今天开始的时间戳
                    $start = strtotime(date("Y-m-d", time())) - 0;
                    //获取今天结束的时间戳
                    $end = strtotime(date("Y-m-d", time())) + 3600 * 24 - 1;
                } elseif ($param == 2) {
                    //本周起始时间
                    $start = date(mktime(0, 0, 0, date("m"), date("d") - date("w") + 1, date("Y")));
                    $end = date(mktime(23, 59, 59, date("m"), date("d") - date("w") + 7, date("Y")));
                } elseif ($param == 3) {
                    //获取本月开始的时间戳
                    $start = mktime(0, 0, 0, $month, 1, $year);
                    $end = mktime(0, 0, 0, $next_month, 1, $year) - 1;
                } elseif ($param == 4) {
                    //获取本年的时间戳
                    $start = mktime(0, 0, 0, 1, 1, $year);
                    $end = mktime(0, 0, 0, 1, 1, $next_year) - 1;
                }
                // 统计块
                $tableDataCount = Db::name("tombarea_row_col")->alias("col")
                    ->field("pay.itype,count(1) count")
                    ->join("tombarea_information_product p", "col.colID = p.tomb_id", "left")
                    ->join("order o", "o.tomb_id = col.colID", "left")
                    ->join("order_pay_log pay", "pay.tomb_id = col.colID", "left")
                    ->join("customer_info info", "o.customer_id = info.id", "left")
                    ->join("archives_deceased ad", "ad.tomb_id = col.colID", "left")
                    ->join("service_buried sb", "sb.tomb_id = col.colID", "left")
                    ->join("service_deposit sd", "sd.tomb_id = col.colID", "left")
                    ->join("service_move sm", "sm.tomb_id = col.colID", "left")
                    ->join("service_weibao sw", "sw.tomb_id = col.colID", "left")
                    ->where("pay.itype", "in", [2, 3, 8, 10])
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)
                    ->group("pay.itype")
                    ->select();
                // 落葬
                // 落葬列表
                $luozang = Db::name("service_buried")->alias("sb")
                    ->field("count(1) count")
                    ->join("tombarea_row_col col", "col.colID = sb.tomb_id ", "left")
                    ->join("order o", "o.order_id = sb.order_id", "left")
                    ->join("tombarea_information_product p", "p.id = o.tomb_product_id", "left")
                    ->join("order_pay_log pay", "pay.id = sb.pay_id", "left")
                    ->join("customer_info info", "o.customer_id = info.id", "left")
                    ->join("archives_deceased ad", "ad.tomb_id = col.colID", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->where("pay.itype", 3)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)->find();
                // 维护费到期统计
                $weihufei = Db::name("order_pay_log")->alias("pay")
                    ->field("count(1) count")
                    ->join("order o", "o.order_id = pay.order_id", "left")
                    ->where("o.whq_end_date", "<", time())
                    ->where("pay.itype", 4)
//                    ->where("pay.is_delete",0)
//                    ->where("pay.type","<>",1)
                    ->find();
                $jicun = Db::name("service_deposit")->alias("sd")
                    ->field("count(1) count")
                    ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                    ->where("pay.itype", 1)
                    ->where("pay.is_delete", 0)
                    ->where("sd.type", 0)->find();
                $jichu = Db::name("service_deposit")->alias("sd")
                    ->field("count(1) count")
                    ->join("customer_info info", "sd.customer_id = info.id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = sd.tomb_id", "left")
                    ->leftJoin("user u", "sd.handle_id = u.id")
                    ->where("sd.type", 1)->find();
                $qianmu = Db::name("service_move")->alias("sd")
                    ->field("count(1) count")
                    ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                    ->where("pay.itype", 8)
//                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)->find();
                // 维保统计信息
                $weibao = Db::name("service_weibao")->alias("sd")
                    ->field("count(1) count")
                    ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                    ->leftJoin("user u", "sd.handle_id = u.id")
                    ->where("pay.itype", 2)
                    ->where("pay.type", "<>", 1)
                    ->where("pay.is_delete", 0)->find();

                $lzrs = Db::name('archives_deceased')->alias('ad')
                    ->field("count(1) count")
                    ->where(['ad.buied_status'=>3,'ad.move_status'=>1,'ad.is_delete'=>1])->find();

                $tuimu = Db::name("retreat_tomb")->alias("rt")
                    ->field("count(1) count")
                    ->join("dj_pay_refund pr", "pr.id = rt.refund_id", "left")
                    ->join("order_pay_log pay", "pr.pay_id = pay.id", "left")
                    ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id ", "left")
                    ->join("customer_info info", "pay.customer_id = info.id", "left")
                    ->leftJoin("user u", "pay.handle = u.id")
                    ->where("pay.is_delete", 0)->find();
                if ($status == 3) {
                    // 落葬列表
                    $tableData = Db::name("service_buried")->alias("sb")
                        ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,col.num,p.selling_price,pay.deal_price,o.closing_price,info.buyer_name,
                    ad.deceased_name,sb.service_status sbStatus,FROM_UNIXTIME(sb.buried_time) appointment_time,FROM_UNIXTIME(sb.funeral_time) complete_time,u.user_nickname")
                        ->join("tombarea_row_col col", "col.colID = sb.tomb_id ", "left")
                        ->join("order o", "o.order_id = sb.order_id", "left")
                        ->join("tombarea_information_product p", "p.id = o.tomb_product_id", "left")
                        ->join("order_pay_log pay", "pay.id = sb.pay_id", "left")
                        ->join("customer_info info", "o.customer_id = info.id", "left")
                        ->join("archives_deceased ad", "ad.buried_id = sb.id", "left")
                        ->leftJoin("user u", "pay.handle = u.id")
                        ->where("pay.itype", 3)
                        ->where("pay.type", "<>", 1)
                        ->where("pay.is_delete", 0)
                        ->whereBetween("o.closing_time", $start . "," . $end)
                        ->where($where)
                        ->order("pay.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()]);
//                    echo Db::name("service_buried")->getLastSql();die;
                } elseif ($status == 0) {
                    //寄存
                    $tableData = Db::name("service_deposit")->alias("sd")
                        ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,sd.tomb_num num,p.selling_price,pay.deal_price,pay.deal_price,info.buyer_name,
                    sd.dead_name deceased_name,sd.service_status sbStatus,FROM_UNIXTIME(sd.deposit_time) appointment_time,FROM_UNIXTIME(sd.add_time) complete_time,sd.type,u.user_nickname")
                        ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                        ->join("customer_info info", "pay.customer_id = info.id", "left")
                        ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                        ->leftJoin("user u", "pay.handle = u.id")
                        ->where("pay.itype", 1)
                        ->where("pay.type", "<>", 1)
                        ->where("pay.is_delete", 0)
                        ->where("sd.type", 0)
                        ->whereBetween("pay.add_time", $start . "," . $end)
                        ->where($where)
                        ->order("pay.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()]);
                } elseif ($status == 1) {
                    //寄出
                    $tableData = Db::name("service_deposit")->alias("sd")
                        ->field("p.tomb_region,p.tomb_address,FROM_UNIXTIME(sd.add_time) add_time,
                    p.tomb_bit_number,sd.tomb_num num,p.selling_price,info.buyer_name,
                    sd.dead_name deceased_name,sd.service_status sbStatus,FROM_UNIXTIME(sd.deposit_time) appointment_time,FROM_UNIXTIME(sd.add_time) complete_time,sd.type,u.user_nickname")
//                        ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                        ->join("customer_info info", "sd.customer_id = info.id", "left")
                        ->join("tombarea_information_product p", "p.tomb_id = sd.tomb_id", "left")
                        ->leftJoin("user u", "sd.handle_id = u.id")
//                        ->where("pay.itype", 1)
//                        ->where("pay.type", "<>", 1)
//                        ->where("pay.is_delete", 0)
                        ->where("sd.type", 1)
                        ->whereBetween("sd.add_time", $start . "," . $end)
                        ->where($where)
                        ->order("sd.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()]);
                } elseif ($status == 8) {
                    // 迁墓列表信息
                    $tableData = Db::name("service_move")->alias("sd")
                        ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,sd.tomb_num num,p.selling_price,pay.deal_price,pay.deal_price,info.buyer_name,
                    sd.death_name deceased_name,sd.service_status sbStatus,FROM_UNIXTIME(sd.move_time) appointment_time,FROM_UNIXTIME(sd.add_time) complete_time,u.user_nickname")
                        ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                        ->join("customer_info info", "pay.customer_id = info.id", "left")
                        ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                        ->leftJoin("user u", "pay.handle = u.id")
                        ->where("pay.itype", 8)
                        ->where("pay.type", "<>", 1)
                        ->where("pay.is_delete", 0)
                        ->whereBetween("pay.add_time", $start . "," . $end)
                        ->where($where)
                        ->order("pay.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()]);
                } elseif ($status == 10) {
                    // 退墓列表信息
                    $tableData = Db::name("retreat_tomb")->alias("rt")
                        ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,p.tomb_num num,pay.tom_price,pay.deal_price,pay.add_time,info.buyer_name,
                    u.user_nickname")
                        ->join("dj_pay_refund pr", "pr.id = rt.refund_id", "left")
                        ->join("order_pay_log pay", "pr.pay_id = pay.id", "left")
                        ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id ", "left")
                        ->join("customer_info info", "pay.customer_id = info.id", "left")
                        ->leftJoin("user u", "pay.handle = u.id")
                        ->where("pay.is_delete", 0)
                        ->whereBetween("pay.add_time", $start . "," . $end)
                        ->where($where)
                        ->order("pay.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()])->toArray();
//                    print_r($tableData);die;
                    for ($i = 0; $i < $tableData['total']; $i++) {
                        $tableData['data'][$i]['itype'] = 10;
                    }
//                    echo Db::name("retreat_tomb")->getLastSql();die;
                } elseif ($status == 2) {
                    // 维保列表信息
                    $tableData = Db::name("service_weibao")->alias("sd")
                        ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,sd.tomb_num num,p.selling_price,pay.deal_price,pay.deal_price,info.buyer_name,
          sd.service_status sbStatus,FROM_UNIXTIME(sd.register_time) appointment_time,FROM_UNIXTIME(sd.deliver_time) complete_time,u.user_nickname")
                        ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                        ->join("customer_info info", "pay.customer_id = info.id", "left")
                        ->join("tombarea_information_product p", "p.tomb_id = pay.tomb_id", "left")
                        ->leftJoin("user u", "sd.handle_id = u.id")
                        ->where("pay.itype", 2)
                        ->where("pay.type", "<>", 1)
                        ->where("pay.is_delete", 0)
                        ->whereBetween("pay.add_time", $start . "," . $end)
                        ->where($where)
                        ->order("pay.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()]);
                } elseif ($status == 5) {
                    $tableData=[];
                } elseif ($status == 4) {
                    // 维护费
                    $tableData = Db::name("protect_payment")->alias("sd")
                        ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,p.tomb_num num,p.selling_price,pay.deal_price,pay.deal_price,info.buyer_name,
          sd.pay_status status,FROM_UNIXTIME(sd.end_time) end_time,sd.end_time endTime ,u.user_nickname")
                        ->join("order_pay_log pay", "pay.id = sd.pay_id", "left")
                        ->join("order o", "o.order_id = sd.order_id", "left")
                        ->join("customer_info info", "pay.customer_id = info.id", "left")
                        ->join("tombarea_information_product p", "p.id = o.tomb_product_id", "left")
                        ->leftJoin("user u", "sd.dealer = u.id")
                        ->where("pay.itype", 4)
                        ->where("pay.type", "<>", 1)
                        ->where("pay.is_delete", 0)
                        ->where("sd.end_time", ">", time())
                        ->whereBetween("pay.add_time", $start . "," . $end)
                        ->where($where)
                        ->order("pay.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()]);
                } else {
                    // 列表信息
                    $tableData = Db::name("tombarea_row_col")->alias("col")
                        ->field("p.tomb_region,pay.itype,p.tomb_address,FROM_UNIXTIME(pay.add_time) add_time,
                    p.tomb_bit_number,col.num,p.selling_price,pay.deal_price,o.closing_price,info.buyer_name,
                    ad.deceased_name,sb.service_status sbStatus,sd.service_status sdstatus,sm.service_status smstatus,sw.service_status swstatus,col.col_status,sd.type sdtype,u.user_nickname")
                        ->join("tombarea_information_product p", "col.colID = p.tomb_id", "left")
                        ->join("order o", "o.tomb_id = col.colID", "left")
                        ->join("order_pay_log pay", "pay.tomb_id = col.colID", "left")
                        ->join("customer_info info", "o.customer_id = info.id", "left")
                        ->join("archives_deceased ad", "ad.tomb_id = col.colID", "left")
                        ->join("service_buried sb", "sb.tomb_id = col.colID", "left")
                        ->join("service_deposit sd", "sd.pay_id = pay.id", "left")
                        ->join("service_move sm", "sm.tomb_id = col.colID", "left")
                        ->join("service_weibao sw", "sw.tomb_id = col.colID", "left")
                        ->leftJoin("user u", "pay.handle = u.id")
                        ->where("pay.itype", "in", [1, 2, 3, 4, 8, 10])
                        ->where("pay.type", "<>", 1)
                        ->where("pay.is_delete", 0)
                        ->whereBetween("o.closing_time", $start . "," . $end)
                        ->where($where)
                        ->order("pay.add_time", "desc")
                        ->paginate(10, false, ['query' => request()->param()]);
                }


//                echo Db::name("tombarea_row_col")->getLastSql();die;
                //获取经办人
                $jingBanRenData = Db::name("user")->alias("u")
                    ->field("u.id,concat(d.department_name,'-',u.user_nickname) user_nickname ,d.dept_type")
                    ->leftJoin("department d", "u.dept_id = d.id")
                    ->where("d.dept_type", "in", [1, 3])
                    ->select()->toArray();
                // 落葬分析
//                $luozang = $this->getData(3);
//                //维保分析图
//                $weibao = $this->getData(2);
//                print_r($luozang[0]);die;
                $datas['categorys'] = $result;
                $datas['tableDataCount'] = $tableDataCount;
                $datas['tableData'] = $tableData;
                $datas['weihufei'] = $weihufei;
                $datas['luozang'] = $luozang;
                $datas['jicun'] = $jicun;
                $datas['jichu'] = $jichu;
                $datas['qianmu'] = $qianmu;
                $datas['weibao'] = $weibao;
                $datas['lzrs'] = $lzrs;
                $datas['tuimu'] = $tuimu;
                $datas['jingBanRenData'] = $jingBanRenData;
//                $datas['luozang'] = $luozang[0];
//                $datas['weibao'] = $weibao[0];
                $this->success("成功", "1", $datas);

                //选中的ID
//                $this->assign("selected_id_v", $this->selected_id_v ? $this->selected_id_v : 0);
            }
            return $this->fetch("serviceindex");
        } catch (Exception $e) {
            echo $e->getMessage();
        }

    }


    public function getData()
    {
        $type = $this->request->post("type");
//        if ($type == 3){
//
//        }
        $sql = "SELECT
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '1' THEN 8 ELSE 0 END ) AS January, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '2' THEN 8 ELSE 0 END ) AS February, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '3' THEN 8 ELSE 0 END ) AS March, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '4' THEN 8 ELSE 0 END ) AS April, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '5' THEN 8 ELSE 0 END ) AS May, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '6' THEN 8 ELSE 0 END ) AS June, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '7' THEN 8 ELSE 0 END ) AS July, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '8' THEN 8 ELSE 0 END ) AS August, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '9' THEN 8 ELSE 0 END ) AS September, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '10' THEN 8 ELSE 0 END ) AS October, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '11' THEN 8 ELSE 0 END ) AS November, 
	sum( CASE MONTH ( FROM_UNIXTIME( `p`.`add_time`, '%Y-%m-%d' ) ) WHEN '12' THEN 8 ELSE 0 END ) AS December
FROM
	`dj_order_pay_log` AS `p` LEFT JOIN dj_order o ON o.order_id = p.order_id where `p`.is_delete = 0 and `p`.itype = " . $type;
        if ($type == 4) {
            $sql .= " and o.whq_end_date < " . time() . " limit 0,1";
        } else {
            $sql .= " limit 0,1";
        }

        $data = Db::query($sql);
//        echo $sql;die;
        $this->success("成功", "", $data[0]);
//        return $data;
    }

    /**
     * 状态统计
     * @return mixed
     */
    public function statusStatistics($ajax=0)
    {
        $selected_id_v = session('selected_id_v');
        try {
            $data = $this->request->param();
            $page = $this->request->get("page", 0);
            $limit = $this->request->get("limit", 10);
            $price = $this->request->param("price", '');
            $didingren = $this->request->param("didingren", '');
            $supplier = $this->request->param("supplier", '');
            $tombId = $this->request->param("tombId", '');
            $status = $this->request->param("status", '');
            $cemetery_model = new CemeteryModel();
            if ($ajax==1){
                //列表信息
                $model = Db::name("tombarea_row_col")->alias("col")
                    ->field("FROM_UNIXTIME(o.closing_time) closing_time,us.user_nickname,col.col_status,p.tomb_region,p.tomb_address,p.tomb_loor,p.tomb_bit_number,col.num,p.selling_price,o.closing_price,info.buyer_name,ad.deceased_name,col.reacher,ip.selling_price selling_price_ip")
                    ->join("order o", "o.tomb_id = col.colID", "left")
                    ->join("tombarea_information_product ip", "ip.tomb_id = col.colID", "left")
                    ->join("tombarea_information_product p", "p.id = o.tomb_product_id", "left")
                    ->join("dj_product d", "d.id = p.new_pro", "left")
                    ->join("dj_purchase_product_order pp", "pp.pro_id = d.id", "left")
                    ->join("dj_purchase e", "e.purchase_id = pp.purchase_id", "left")
                    ->join("user us", "us.id = e.supplier", "left")
                    ->join("customer_info info", "o.customer_id = info.id", "left")
                    ->join("archives_deceased ad", "ad.order_id = o.order_id", "left")
                    ->where(['col.status'=>1]);
                if (!empty($didingren)) {
                    $model = $model->where('col.reacher',$didingren);
                }
                if (!empty($price)) {
                    $model = $model->where('ip.selling_price',$price);
                }
                if (!empty($supplier)) {
                    $model = $model->where('p.supplier',$supplier);
                }
                if (!empty($tombId)) {
                    $model = $model->whereIn('col.tombarea_id',$tombId);
                }
                if (!empty($status)) {
                    $model = $model->where('col.col_status',$status);
                }else{
//                    $model = $model->where("col.col_status", ">", "1");
                }
                $tableData = $model->limit($page,$limit)->select();
                $tableTotal =$model->count();
                $model_sum = Db::name("tombarea_row_col")->alias("col")->field("col.col_status,count(1) count")
                    ->join("order o", "o.tomb_id = col.colID", "left")
                    ->join("tombarea_information_product p", "p.id = o.tomb_product_id", "left")
                    ->join("dj_product d", "d.id = p.new_pro", "left")
                    ->join("dj_purchase_product_order pp", "pp.pro_id = d.id", "left")
                    ->join("dj_purchase e", "e.purchase_id = pp.purchase_id", "left")
                    ->join("user us", "us.id = e.supplier", "left")
                    ->join("customer_info info", "o.customer_id = info.id", "left")
                    ->join("archives_deceased ad", "ad.order_id = o.order_id", "left")
                    ->group("col.col_status");

                if (!empty($tombId)) {
                    $model_sum = $model_sum->whereIn('col.tombarea_id',$tombId);
                }
                $colSum = $model_sum->select();
                $result = array("total" => $tableTotal, "rows" => $tableData,'zcount_sum'=>$colSum);
                exit(json_encode($result));
            }else{
                $result = $cemetery_model->field("id,name,parent_id as pId,type")->where(array('is_delete' => 0))
                        ->order(array("list_order" => "ASC"))->select()->toArray();
                $tom = array();
                foreach ($result as $k => $v) {
                    $tombarea = Db::name("tombarea")->field("id,name,'' as type")
                        ->where(array("cemetery_id" => $v["id"], "status" => 1))->order('list_order asc ')
                        ->select()->toArray();
                    foreach ($tombarea as $i => $t) {
                        $tombarea[$i]['pId'] = $v["id"];
                    }
                    $tom = array_merge_recursive($tom, $tombarea);
                }
                $result = array_merge_recursive($tom, $result);
                $this->assign("selected_id_v", $selected_id_v?$selected_id_v:0);
                // 获取价位
                $price = Db::name("tombarea_row_col")->alias("c")->distinct(true)->field("p.selling_price")
                    ->join("tombarea_information_product p", "c.colID = p.tomb_id", "right")->select()->toArray();
                //查抵顶人
                $didingren = Db::name("user")->alias("u")->field("u.id,u.user_nickname")
                    ->join("department d", "u.dept_id = d.id", "left")->where(array("d.dept_type" => "7"))->select()->toArray();
                //查供货商
                $supplier = Db::name("user")->alias("u")->field("u.id,u.user_nickname")
                    ->join("department d", "u.dept_id = d.id", "left")->where(array("d.dept_type" => "5"))->select()->toArray();

                $datas['categorys'] = $result;
                $datas['price'] = $price;
                $datas['didingren'] = $didingren;
                $datas['supplier'] = $supplier;
                $this->assign('result_data',json_encode($datas));
                $this->assign("selected_id_v", $this->selected_id_v ? $this->selected_id_v : 0);
//            if ($data) {
//                $this->success("成功", "1", $datas);
//            }
            }


            return $this->fetch("statusindex");
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }




    /**
     * 左侧树
     * @return mixed
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getTree($name)
    {
        $this->cemetery_model = Db::name("cemetery");
//        $name = $this->request->param("name");
        //import("Tree");
        $menu = new Tree();
        $menu->icon = array('&nbsp;', '&nbsp;', '&nbsp;');
        $menu->nbsp = '';

        $result = $this->cemetery_model->where(array('is_delete' => 0))->where('name', 'like', '%' . $name . '%')
            ->order(array("list_order" => "ASC"))->select()->toArray();
        if (!empty($name)) {
            for ($i = 0; $i < count($result); $i++) {
                $arr1 = $this->getSup($result[$i]["parent_id"]);
                $imgInfo = array_merge_recursive($arr1, $result);
            }
            $key = "id";//去重条件
            $tmp_arr = array();//声明数组
            foreach ($imgInfo as $k => $v) {
                if (in_array($v[$key], $tmp_arr)) { //搜索$v[$key]是否在$tmp_arr数组中存在，若存在返回true
                    unset($imgInfo[$k]);//删除掉数组（$arr）里相同ID的数组
                } else {
                    $tmp_arr[] = $v[$key];//记录已有的id
                }
            }
            $result = $imgInfo;
        }
        $newmenus = array();
        foreach ($result as $m) {
            $newmenus[$m['id']] = $m;
        }
        foreach ($result as $n => $t) {
            $result[$n]['level'] = $this->_get_level($t['id'], $newmenus);
            $result[$n]['parentid_node'] = ($t['parent_id']) ? ' class="child-of-node-' . $t['parent_id'] . '"' : '';
        }
        $tom = array();
        foreach ($result as $k => $v) {
            $tombarea = Db::name("tombarea")->field("id,name,'' as type,1 as status,'' as remark,'' as cover_path,'' as list_order,3 as is_generate,0 as is_delete")
                ->where(array("cemetery_id" => $v["id"], "status" => 1))
                ->select()->toArray();
            foreach ($tombarea as $i => $t) {
                $tombarea[$i]['parent_id'] = $v["id"];
                $tombarea[$i]['level'] = $v["level"] + 1;
                $tombarea[$i]['parentid_node'] = ' class="child-of-node-' . $v["id"] . '"';
            }
            $tom = array_merge_recursive($tom, $tombarea);
        }

        $result = array_merge_recursive($tom, $result);

        $str = "<tr id='node-\$id' \$parentid_node>
				   <td style='padding-left:30px; cursor:pointer'><span name='title' id='\$id' data-parent='\$is_generate' \$parentid_node>\$spacer \$name</span></td>
				</tr>";
        $menu->init($result);
        $categorys = $menu->getTree(0, $str);
        return $categorys;
//        $this->assign("categorys", $categorys);
//        $this->assign("name", $name);
//        return $this->fetch("serviceindex");
    }

    /**
     * 获取菜单深度
     * @param $id
     * @param $array
     * @param $i
     */
    protected function _get_level($id, $array = array(), $i = 0)
    {

        if ($array[$id]['parent_id'] == 0 || empty($array[$array[$id]['parent_id']]) || $array[$id]['parent_id'] == $id) {
            return $i;
        } else {
            $i++;
            return $this->_get_level($array[$id]['parent_id'], $array, $i);
        }

    }

    public function getSup($id)
    {
        // 拼接查询条件hql
        $res = Db::name("cemetery")->where(array('is_delete' => 0, 'id' => $id))
            ->select()->toArray();
        // 得到一级数据
        if ($res) {
            if ($res[0]["parent_id"] != 0) {
                $result = $this->getSup($res[0]["parent_id"]);
            } else {
                $result = array();
            }
            $imgInfo = array_merge_recursive($res, $result);
        } else {
            $imgInfo = array();
        }
        return $imgInfo;
    }


}